﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DataLib.DBUtility;//Please add references
using DataLib.Accounts.Bus;

namespace DataLib.DAL
{
	/// <summary>
	/// 数据访问类:cum_aacounts
	/// </summary>
	public partial class cum_aacounts
	{
		public cum_aacounts()
		{}
		#region  BasicMethod

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("cumid", "cum_aacounts"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int cumid,int accountid)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from cum_aacounts");
			strSql.Append(" where cumid=@cumid and accountid=@accountid ");
			SqlParameter[] parameters = {
					new SqlParameter("@cumid", SqlDbType.Int,4),
					new SqlParameter("@accountid", SqlDbType.Int,4)			};
			parameters[0].Value = cumid;
			parameters[1].Value = accountid;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public bool Add(DataLib.Model.cum_aacounts model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into cum_aacounts(");
			strSql.Append("cumid,accountid)");
			strSql.Append(" values (");
			strSql.Append("@cumid,@accountid)");
			SqlParameter[] parameters = {
					new SqlParameter("@cumid", SqlDbType.Int,4),
					new SqlParameter("@accountid", SqlDbType.Int,4)};
			parameters[0].Value = model.cumid;
			parameters[1].Value = model.accountid;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(DataLib.Model.cum_aacounts model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update cum_aacounts set ");
			strSql.Append("cumid=@cumid,");
			strSql.Append("accountid=@accountid");
			strSql.Append(" where accountid=@accountid ");
			SqlParameter[] parameters = {
					new SqlParameter("@cumid", SqlDbType.Int,4),
					new SqlParameter("@accountid", SqlDbType.Int,4)};
			parameters[0].Value = model.cumid;
			parameters[1].Value = model.accountid;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int cumid,int accountid)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from cum_aacounts ");
			strSql.Append(" where cumid=@cumid and accountid=@accountid ");
			SqlParameter[] parameters = {
					new SqlParameter("@cumid", SqlDbType.Int,4),
					new SqlParameter("@accountid", SqlDbType.Int,4)			};
			parameters[0].Value = cumid;
			parameters[1].Value = accountid;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public DataLib.Model.cum_aacounts GetModel(int cumid,int accountid)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 cumid,accountid from cum_aacounts ");
			strSql.Append(" where cumid=@cumid and accountid=@accountid ");
			SqlParameter[] parameters = {
					new SqlParameter("@cumid", SqlDbType.Int,4),
					new SqlParameter("@accountid", SqlDbType.Int,4)			};
			parameters[0].Value = cumid;
			parameters[1].Value = accountid;

			DataLib.Model.cum_aacounts model=new DataLib.Model.cum_aacounts();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public DataLib.Model.cum_aacounts DataRowToModel(DataRow row)
		{
			DataLib.Model.cum_aacounts model=new DataLib.Model.cum_aacounts();
			if (row != null)
			{
				if(row["cumid"]!=null && row["cumid"].ToString()!="")
				{
					model.cumid=int.Parse(row["cumid"].ToString());
				}
				if(row["accountid"]!=null && row["accountid"].ToString()!="")
				{
					model.accountid=int.Parse(row["accountid"].ToString());
				}
			}
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select cumid,accountid ");
			strSql.Append(" FROM cum_aacounts ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" cumid,accountid ");
			strSql.Append(" FROM cum_aacounts ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM cum_aacounts ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.accountid desc");
			}
			strSql.Append(")AS Row, T.*  from cum_aacounts T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "cum_aacounts";
			parameters[1].Value = "accountid";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

        public DataTable GetCumAccountList(string belong)
        {
            DataTable dt = new DataTable();
            SqlParameter[] parameters = {
                   new SqlParameter("@belong",SqlDbType.VarChar,25)
                                       };
            parameters[0].Value = belong;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_CumAccountList", parameters);
            dt.Load(dr);
            dr.Close();
            return dt;
           
        }

        
        


		#endregion  BasicMethod
		#region  ExtensionMethod
        /// <summary>
        /// 获取LTR数据集
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public DataTable getLTRWhere(string strwhere)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@strwhere",SqlDbType.VarChar,50)
             };
            sqlparameter[0].Value = strwhere;
            SqlDataReader dr = DbHelperSQL.RunProcedure("Ewts_LTR_SEAR", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 根据userid获取客户帐号
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public Model.Cumtomer GetCumByUserid(int userid)
        {
            Model.Cumtomer cum = new Model.Cumtomer();
            Cumtomer dalcum = new Cumtomer();
            DataTable dt = GetList("accountid=" + userid.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                int cumid = int.Parse(dt.Rows[0]["cumid"].ToString().Trim());
                cum = dalcum.GetModel(cumid);
            }
            else
            {
                cum = null;
            }
            return cum;
        }

        /// <summary>
        /// 根据客户ID取得用户帐号
        /// </summary>
        /// <param name="comid"></param>
        /// <returns></returns>
        public User getUserByComId(int comid)
        {
            User currUser;
            DataTable dt = GetList("cumid=" + comid.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                int userid = int.Parse(dt.Rows[0]["accountid"].ToString().Trim());
                currUser = new User(userid);
            }
            else
            {
                currUser = null;
            }
            
            return currUser;
        }

        /// <summary>
        /// 根据登陆用户获取客户的LoadNum列表
        /// </summary>
        /// <param name="blongCity">归属城市</param>
        /// <param name="Depart">登陆人员部门</param>
        /// <param name="userid">用户的系统ID</param>
        /// <param name="username">用户帐号</param>
        /// <param name="usertype">用户类型</param>
        /// <returns></returns>
        public DataTable GETLoadListByCumtomer(int userid, string username,string usertype,string blongCity,string Depart)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                 new SqlParameter("@UserID",SqlDbType.Int)
                ,new SqlParameter("@UserName",SqlDbType.NVarChar,20)
                ,new SqlParameter("@UserType",SqlDbType.NVarChar,2)
                ,new SqlParameter("@BelongCity",SqlDbType.NVarChar,20)
                ,new SqlParameter("@Depart",SqlDbType.NVarChar,20)

             };
            sqlparameter[0].Value = userid;
            sqlparameter[1].Value = username;
            sqlparameter[2].Value = usertype;
            sqlparameter[3].Value = blongCity;
            sqlparameter[4].Value = Depart;
            DataSet ds = DbHelperSQL.RunProcedure("EWMS_GETLoadListByCumtomer", sqlparameter, "LoadList");
            return ds.Tables[0];
        }

        /// <summary>
        /// 根据登陆用户获取客户的LoadNum列表
        /// </summary>
        /// <param name="blongCity">归属城市</param>
        /// <param name="Depart">登陆人员部门</param>
        /// <param name="userid">用户的系统ID</param>
        /// <param name="username">用户帐号</param>
        /// <param name="usertype">用户类型</param>
        /// <returns></returns>
        public DataTable GETBarByCumtomer(int userid, string username, string usertype, string blongCity,string loadno, string goodsName,string strtDate, string endDate,int curStatus,out DataTable BarTab)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                 new SqlParameter("@UserID",SqlDbType.Int)
                ,new SqlParameter("@UserName",SqlDbType.NVarChar,20)
                ,new SqlParameter("@UserType",SqlDbType.NVarChar,2)
                ,new SqlParameter("@BelongCity",SqlDbType.NVarChar,20)
                ,new SqlParameter("@LoadNum",SqlDbType.NVarChar,15)
                ,new SqlParameter("@StartDate",SqlDbType.DateTime)
                ,new SqlParameter("@EndDate",SqlDbType.DateTime)
                ,new SqlParameter("@goodsName",SqlDbType.NVarChar,20)
               ,new SqlParameter("@currStatus",SqlDbType.Int)
             };
            sqlparameter[0].Value = userid;
            sqlparameter[1].Value = username; 
            sqlparameter[2].Value = usertype;
            sqlparameter[3].Value = blongCity;
            sqlparameter[4].Value = loadno;
            if (strtDate != null)
            {
                DateTime sDate = DateTime.Parse(strtDate);
                sqlparameter[5].Value = sDate.AddDays(-1).AddDays(1);
            }
            if (endDate != null)
            {
                DateTime eDate = DateTime.Parse(endDate);
                sqlparameter[6].Value = eDate.AddDays(1).AddSeconds(-1);
            }
            sqlparameter[7].Value = goodsName;
            sqlparameter[8].Value = curStatus;
            DataSet ds = DbHelperSQL.RunProcedure("EWMS_GETBarCodeByCumtomer", sqlparameter, "LoadList");
            dt = ds.Tables[0];
            BarTab = ds.Tables[1];
            return dt;
        }


        public DataTable CumAccountListDetile(int cumid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] parameters = { 
                new SqlParameter("@cumid", SqlDbType.Int, 4) 
             };
            parameters[0].Value = cumid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_CumAccountListDetile", parameters);
            dt.Load(dr);
            dr.Close();
            return dt;
            
            

        }


        /// <summary>
        /// 获取LTR数据集
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public DataTable GetLtrList(string loadno, string loaddate
                ,string cumcode, string cumname, string colldate, string collmonth, string gcity
                , string CEcaiwu, string CAcum, string CumSname, string cjosaleman, string remark
                , string nwt,string wrldate,string goodsinfo,string tallydate,string proname
                ,string bcity)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@Loadno",SqlDbType.VarChar,20)
                ,new SqlParameter("@LoadDate",SqlDbType.VarChar,20)
                ,new SqlParameter("@cumcode",SqlDbType.VarChar,50)
                ,new SqlParameter("@cumname",SqlDbType.VarChar,50)
                ,new SqlParameter("@colldate",SqlDbType.VarChar,20)
                ,new SqlParameter("@collmonth",SqlDbType.VarChar,2)
                ,new SqlParameter("@gcity",SqlDbType.VarChar,20)
                ,new SqlParameter("@CEcaiwu",SqlDbType.VarChar,20)
                ,new SqlParameter("@CAcum",SqlDbType.VarChar,20)
                ,new SqlParameter("@CumSname",SqlDbType.VarChar,20)
                ,new SqlParameter("@cjosaleman",SqlDbType.VarChar,20)
                ,new SqlParameter("@remark",SqlDbType.VarChar,50)
                ,new SqlParameter("@nwt",SqlDbType.Decimal)
                ,new SqlParameter("@wrldate",SqlDbType.VarChar,20)
                ,new SqlParameter("@goodsinfo",SqlDbType.VarChar,40)
                ,new SqlParameter("@tallydate",SqlDbType.VarChar,20)
                ,new SqlParameter("@proname",SqlDbType.VarChar,30)
                ,new SqlParameter("@bcity",SqlDbType.VarChar,20)
             };
            sqlparameter[0].Value = loadno;
            sqlparameter[1].Value = loaddate;
            sqlparameter[2].Value = cumcode;
            sqlparameter[3].Value = cumname;
            sqlparameter[4].Value = colldate;
            if (colldate != "")
            {
                sqlparameter[5].Value = collmonth;
            }
            sqlparameter[6].Value = gcity;
            sqlparameter[7].Value = CEcaiwu;
            sqlparameter[8].Value = CAcum;
            sqlparameter[9].Value = CumSname;
            sqlparameter[10].Value = cjosaleman;
            sqlparameter[11].Value = remark;
            if (nwt != "")
            {
                sqlparameter[12].Value = nwt;
            }
            sqlparameter[13].Value = wrldate;
            sqlparameter[14].Value = goodsinfo;
            sqlparameter[15].Value = tallydate;
            sqlparameter[16].Value = proname;
            sqlparameter[17].Value = bcity;

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GET_LTRList", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }


        /// <summary>
        /// 获取
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public String GetCumUserName(string cumcode)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@cumcode",SqlDbType.VarChar,50)
             };
            sqlparameter[0].Value = cumcode;
            SqlDataReader dr = DbHelperSQL.RunProcedure("GetCumCodeUserName", sqlparameter);
            dt.Load(dr);
            dr.Close();
            string cumusername = "";
            if (dt != null && dt.Rows.Count == 1)
            {
                cumusername = dt.Rows[0][0].ToString();
                string right3 = cumusername.Substring(cumusername.Length - 3, 3);
                right3 = (int.Parse(right3) + 1).ToString().PadLeft(3, '0');
                cumusername = cumusername.Substring(0, cumusername.Length - 3) + right3;
            }
            return cumusername;
        }

        /// <summary>
        /// 根据客户ID取得客户下的所有帐号列表
        /// </summary>
        /// <param name="cumid"></param>
        /// <returns></returns>
        public DataTable GetUserListByCumid(string cumid)
        {
            DataTable dt = new DataTable();
            string sql = "";
   
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select * from Accounts_Users ");
			strSql.Append(" where UserID in (");
            strSql.Append(" select accountid from cum_aacounts where cumid=");
            strSql.Append( cumid + ")");
		
			dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

		#endregion  ExtensionMethod
	}
}

